package com.fdms.aurora;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Properties;
import java.util.TreeMap;

import org.apache.commons.lang.math.NumberUtils;
import org.apache.poi.hpsf.VariantTypeException;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import fdms.ui.struts.form.FinancialInformationForm;

public class FinancialInformationExcel {

	private Properties properties = new Properties();
	HashMap<String,String>  invMasters = new HashMap<String, String>();
	HashMap<String,String>  serviceItmes = new HashMap<String, String>();
	HashMap<String,String>  packageItmes = new HashMap<String, String>();
	InputStream myxls;
	XSSFWorkbook wb ;
 	
	
	public FinancialInformationExcel(String templateName) {
		try {
			properties.load(new FileInputStream(new File("validationFinancialInfo.properties")));
			myxls = new FileInputStream(templateName);
			wb= new XSSFWorkbook(myxls);

			
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	public HashMap<String,String> convertIntoHashMap(String sheetName)
	{
		HashMap<String,String>  items = new HashMap<String, String>();
		XSSFSheet sheet = wb.getSheet(sheetName);      
		
		XSSFRow records  ;
		for(int row = 1; row <= sheet.getLastRowNum(); row++) {
			records   = sheet.getRow(row);        // column/field row
			records.getCell(0).setCellType(1);
			items.put(records.getCell(1).getStringCellValue(), records.getCell(0).getStringCellValue());
		}
		return items;
		
	}
	public String[] convertStringListToArray(ArrayList<String> stringList){
		String[] stringArray = new String[stringList.size()];
		int i=0;
		for(String items : stringList){
			 
			stringArray[i]= items;
			System.out.println("invItemsID : "+ items);
			i++;
		}
		return stringArray;
	}
	public FinancialSuper readInfoFromExcel() throws Exception{
		FinancialSuper financeSuper= new FinancialSuper();
		Utility.initRejectXLS();
		try{
			String fieldValue="";			
			XSSFSheet sheet = wb.getSheet("FinancialInformation");       // first sheet
			System.out.println("Total no of rows in Financial Tabs :: "+ sheet.getLastRowNum() );
					
			XSSFRow columnHeading   = sheet.getRow(1);        // column/field row
		    
			//Field row
			HashMap<Integer,String> validation= new HashMap<Integer,String>();
			// read column names
			for(int index = 0; index <  columnHeading.getLastCellNum(); index++) {
				fieldValue=columnHeading.getCell(index).getStringCellValue();
				if(properties.containsKey(fieldValue)){
					validation.put(index, fieldValue);
					//System.out.println("added validation "+ index);
				}
			}
			XSSFRow dataRow=null;
			
			ArrayList<Integer> validRows= checkVailidRows(sheet,validation);
			System.out.println("Total No of Valid Row in Financial Tab :: "+ validRows);
			int index=0;
			for(int startRow= 0;startRow<validRows.size();startRow++ ){
				try {	
					dataRow=sheet.getRow(validRows.get(startRow).intValue());
					financeSuper = getFinancialBeanData(dataRow,123);
					
				}catch(Exception ex){
					System.out.println("Error for reading excel row " + startRow + " and column "+ index );
					System.out.println("Error :: "+ ex.getMessage());
					ex.printStackTrace();
					break;
				}
			}
		}catch (Exception e) {
			e.printStackTrace();
		}
		Utility.createRejectXls();
		return financeSuper;
	}
	public FinancialSuper getFinancialBeanData (XSSFRow rowData,int vitalsId) throws Exception
	{
		FinancialSuper financialSuper= new FinancialSuper();
		try{
			
			
			invMasters= convertIntoHashMap("invitem");
			serviceItmes= convertIntoHashMap("pricelist");
			packageItmes = convertIntoHashMap("Packages");
			String[] serviceListValue;
			String[] invItemListValue;
			String[] packageListValue;
			financialSuper.getFinancialForm().setVitalsId(vitalsId);
			financialSuper.getFinancialForm().setShowFinancingSection( (getValidCellValue(rowData.getCell(1)).equals("Y"))?true:false);
			financialSuper.getFinancialForm().setApplyFinanceCharges((getValidCellValue(rowData.getCell(2)).equals("Y"))?true:false);
			financialSuper.getFinancialForm().setInterestRate(getValidCellValue(rowData.getCell(3)));
			financialSuper.getFinancialForm().setInterestFromDate(getValidCellValue(rowData.getCell(4)));
			financialSuper.getFinancialForm().setCustomerName(getValidCellValue(rowData.getCell(5)));
			financialSuper.getFinancialForm().setPostedContract(getValidCellValue(rowData.getCell(6)));
			financialSuper.getFinancialForm().setShowFinancingSection((getValidCellValue(rowData.getCell(7)).equals("Y"))?true:false);
			financialSuper.getFinancialForm().setSaleDate(getValidCellValue(rowData.getCell(8)));
			financialSuper.getFinancialForm().setDueDate(getValidCellValue(rowData.getCell(9)));
			financialSuper.getFinancialForm().setStmtDate(getValidCellValue(rowData.getCell(10)));
			financialSuper.getFinancialForm().setAmountPaid(getValidCellValue(rowData.getCell(11)));
			financialSuper.getFinancialForm().setPaymentSource(getValidCellValue(rowData.getCell(12)));
			financialSuper.getFinancialForm().setDateOfPayment(getValidCellValue(rowData.getCell(13)));
			financialSuper.getFinancialForm().setDateOfDeposit(getValidCellValue(rowData.getCell(14)));
			financialSuper.getFinancialForm().setReceiptNumber(getValidCellValue(rowData.getCell(15)));
			financialSuper.getFinancialForm().setManualReceiptNumber(getValidCellValue(rowData.getCell(16)));
			financialSuper.getFinancialForm().setNonCashAdjustment(getValidCellValue(rowData.getCell(17)));
			financialSuper.getFinancialForm().setMethodOfPayment(getValidCellValue(rowData.getCell(18)));
			financialSuper.getFinancialForm().setSalesDescCDID(getValidCellValue(rowData.getCell(19)));
			financialSuper.getFinancialForm().setSaleType(getValidCellValue(rowData.getCell(20)));
			financialSuper.getFinancialForm().setSource(getValidCellValue(rowData.getCell(21)));
			financialSuper.getFinancialForm().setDisposition(getValidCellValue(rowData.getCell(22)));

			financialSuper.getFinancialForm().setServicePlan(getValidCellValue(rowData.getCell(23)));
			financialSuper.getFinancialForm().setProvidedServices(getValidCellValue(rowData.getCell(24)));
			financialSuper.getFinancialForm().setPreviousFuneralHomeUsed(getValidCellValue(rowData.getCell(25)));
			financialSuper.getFinancialForm().setAdvertisingSource(getValidCellValue(rowData.getCell(26)));
			financialSuper.getFinancialForm().setMiscNotes(getValidCellValue(rowData.getCell(27)));
			
			ArrayList<String> invItems = new ArrayList<String>();
			ArrayList<String> serviceItem = new ArrayList<String>();
			ArrayList<String> packageItem= new ArrayList<String>();                 
			for (int column =28; column <= rowData.getLastCellNum();column+=2 )
				if(rowData.getCell(column)!= null && rowData.getCell(column+1)!= null)
				{
					if(rowData.getCell(column).getStringCellValue().equalsIgnoreCase("M")){
						if(invMasters.containsKey(rowData.getCell(column+1).getStringCellValue())){
							invItems.add(invMasters.get(rowData.getCell(column+1).getStringCellValue()));
						}
					}
					if(rowData.getCell(column).getStringCellValue().equalsIgnoreCase("S")){
						if(serviceItmes.containsKey(rowData.getCell(column+1).getStringCellValue())){
							serviceItem.add(serviceItmes.get(rowData.getCell(column+1).getStringCellValue()));
						}
					}
					if(rowData.getCell(column).getStringCellValue().equalsIgnoreCase("P")){
						if(packageItmes.containsKey(rowData.getCell(column+1).getStringCellValue())){
							packageItem.add(packageItmes.get(rowData.getCell(column+1).getStringCellValue()));
						}
					}
					
				}
			System.out.println("Total no of Inv Items :: "+invItems.size());
			System.out.println("Total no of Service Items :: "+serviceItem.size());
			
		
			invItemListValue = new String[invItems.size()];
			serviceListValue = new String[serviceItem.size()];
			packageListValue = new String[packageItem.size()];
			
			invItemListValue = convertStringListToArray(invItems);
			serviceListValue = convertStringListToArray(serviceItem);
			packageListValue = convertStringListToArray(packageItem);
			
	
			//set line items from Inventory Master
			financialSuper.getMerchandiseForm().setSubmitButton("save");
			financialSuper.getMerchandiseForm().setListValue(invItemListValue);
			
			//set line items from Services(Pricelist)
			financialSuper.getServiceForm().setSubmitButton("save");
			financialSuper.getServiceForm().setListValue(serviceListValue);
			
			//set line items from Packages
			for(int i=0; i<packageListValue.length;i++){
				financialSuper.getPackageForm().setSubmitbutton("save");
				financialSuper.getPackageForm().setPkgPriceListId(packageListValue[i]);
				financialSuper.getPackageForm().setPriceListVersion("PriceList");
			}
		}catch(Exception ex){
			throw ex;
		}
		return financialSuper;
	}
	public String getValidCellValue(XSSFCell cell) throws Exception{
		 String validValue="";
		 try{
			 if(cell !=null){
				 switch(cell.getCellType()){
				 	case 0: // Numeric value
				 		validValue = String.valueOf(cell.getNumericCellValue());
				 		break;
				 	case 1 : // String value
				 		if(cell.getStringCellValue().equals("(NULL)")){
				    		validValue="";
				    	}else{
				    		validValue=cell.getStringCellValue();
				    	}
				 		break;
				 	case 3 : //null value
				 		validValue="";
				 		break;
					case 4 : // boolean value
						validValue= String.valueOf(cell.getBooleanCellValue());
						break;
					case 5://Error value
						validValue="";
						break;
					default:
						validValue="";
				 }
			 }
		 }catch(Exception ex){
			 throw ex;
		 }
		 return validValue;
	 }
	public ArrayList<Integer> checkVailidRows(XSSFSheet sheet,HashMap<Integer,String> validation){ 
		ArrayList<Integer> validRows= new ArrayList<Integer>();
		XSSFRow data=null;
			for( int startRow= 2;startRow<=sheet.getLastRowNum();startRow++ ){
				try {
					data=sheet.getRow(startRow);
					boolean validRowData = true;
					for (Integer key : validation.keySet()) {
					   int colIndex=key.intValue();
					   //System.out.println("colINdex :: " + colIndex);
					   XSSFCell cell = data.getCell(colIndex);
						// validate data of excel row
					  // System.out.println("get Validate on : "+ validation.get(key));
					   	if(performValidation(validation.get(key), data,cell)==true){
					   		validRowData=true;
					   	}else{
					   		validRowData=false;
					   		System.out.println("Validation fail on row :: "+ data.getRowNum());
					   		break;
					   	}
					}
					if(validRowData){
						//System.out.println("Valid rows no is :: "+ startRow);
						validRows.add(Integer.valueOf(startRow));
					}
				}catch(Exception ex){
					ex.printStackTrace();
				}
			}
			
		 return validRows;
	 }
	public boolean performValidation(String validationName,XSSFRow row,XSSFCell cell){
		//System.out.println("Validation perform Name :: "+validationName);
		
			if(validationName.equalsIgnoreCase("salesDescCDID")){
				if(cell==null || cell.getNumericCellValue()<=0){
					Utility.addErrorRow(row);
					Utility.addErrorDescriptionRow(row.getRowNum(),validationName, properties.getProperty(validationName));
					Utility.rowCount++;
				}else{
					return true;
				}
			}else if(validationName.equalsIgnoreCase("saleType")){
				if(cell==null || cell.getStringCellValue().equals("(NULL)")||cell.getStringCellValue().trim().length()<=0){
					Utility.addErrorRow(row);
					Utility.addErrorDescriptionRow(row.getRowNum(),validationName, properties.getProperty(validationName));
					Utility.rowCount++;
				}else{
					return true;
				}
			}else if(validationName.equalsIgnoreCase("interestFromDate")){
				cell.setCellType(1);
					if(cell==null || cell.getStringCellValue().trim().length()<=0){
					Utility.addErrorRow(row);
					Utility.addErrorDescriptionRow(row.getRowNum(),validationName, properties.getProperty(validationName));
					Utility.rowCount++;
				}else if(cell.getStringCellValue().equals("(NULL)")) {
					return true;
				}else
					try{
						Utility.convertToDateMMDDYYYY(cell.getStringCellValue());
						return true;
					}catch(Exception ex){
						//ex.printStackTrace();
						Utility.addErrorRow(row);
						Utility.addErrorDescriptionRow(row.getRowNum(),validationName, properties.getProperty(validationName));
						Utility.rowCount++;
						return false;
					}
			}else if(validationName.equalsIgnoreCase("saleDate")){
				cell.setCellType(1);
				if(cell==null || cell.getStringCellValue().trim().length()<=0){
					Utility.addErrorRow(row);
					Utility.addErrorDescriptionRow(row.getRowNum(),validationName, properties.getProperty(validationName));
					Utility.rowCount++;
				}else if(cell.getStringCellValue().equals("(NULL)")) {
					return true;
				}else
					try{
						Utility.convertToDateMMDDYYYY(cell.getStringCellValue());
						return true;
					}catch(Exception ex){
						//ex.printStackTrace();
						Utility.addErrorRow(row);
						Utility.addErrorDescriptionRow(row.getRowNum(),validationName, properties.getProperty(validationName));
						Utility.rowCount++;
						return false;
					}
			}else if(validationName.equalsIgnoreCase("dueDate")){
				cell.setCellType(1);
				if(cell==null || cell.getStringCellValue().trim().length()<=0){
					Utility.addErrorRow(row);
					Utility.addErrorDescriptionRow(row.getRowNum(),validationName, properties.getProperty(validationName));
					Utility.rowCount++;
				}else if(cell.getStringCellValue().equals("(NULL)")) {
					return true;
				}else
					try{
						Utility.convertToDateMMDDYYYY(cell.getStringCellValue());
						return true;
					}catch(Exception ex){
						//ex.printStackTrace();
						Utility.addErrorRow(row);
						Utility.addErrorDescriptionRow(row.getRowNum(),validationName, properties.getProperty(validationName));
						Utility.rowCount++;
						return false;
					}
			}else if(validationName.equalsIgnoreCase("stmtDate")){
				cell.setCellType(1);
				if(cell==null || cell.getStringCellValue().trim().length()<=0){
					Utility.addErrorRow(row);
					Utility.addErrorDescriptionRow(row.getRowNum(),validationName, properties.getProperty(validationName));
					Utility.rowCount++;
				}else if(cell.getStringCellValue().equals("(NULL)")) {
					return true;
				}else
					try{
						Utility.convertToDateMMDDYYYY(cell.getStringCellValue());
						return true;
					}catch(Exception ex){
						//ex.printStackTrace();
						Utility.addErrorRow(row);
						Utility.addErrorDescriptionRow(row.getRowNum(),validationName, properties.getProperty(validationName));
						Utility.rowCount++;
						return false;
					}
			}else if(validationName.equalsIgnoreCase("dateOfPayment")){
				cell.setCellType(1);
				if(cell==null || cell.getStringCellValue().trim().length()<=0){
					Utility.addErrorRow(row);
					Utility.addErrorDescriptionRow(row.getRowNum(),validationName, properties.getProperty(validationName));
					Utility.rowCount++;
				}else if(cell.getStringCellValue().equals("(NULL)")) {
					return true;
				}else
					try{
						Utility.convertToDateMMDDYYYY(cell.getStringCellValue());
						return true;
					}catch(Exception ex){
						//ex.printStackTrace();
						Utility.addErrorRow(row);
						Utility.addErrorDescriptionRow(row.getRowNum(),validationName, properties.getProperty(validationName));
						Utility.rowCount++;
						return false;
					}
			}else if(validationName.equalsIgnoreCase("dateOfDeposit")){
				cell.setCellType(1);
				if(cell==null || cell.getStringCellValue().trim().length()<=0){
					Utility.addErrorRow(row);
					Utility.addErrorDescriptionRow(row.getRowNum(),validationName, properties.getProperty(validationName));
					Utility.rowCount++;
				}else if(cell.getStringCellValue().equals("(NULL)")) {
					return true;
				}else
					try{
						Utility.convertToDateMMDDYYYY(cell.getStringCellValue());
						return true;
					}catch(Exception ex){
						//ex.printStackTrace();
						Utility.addErrorRow(row);
						Utility.addErrorDescriptionRow(row.getRowNum(),validationName, properties.getProperty(validationName));
						Utility.rowCount++;
						return false;
					}
			}
			return false;
		}
}
